import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import random
import matplotlib.pyplot as plt
import seaborn as sns
from tabulate import tabulate
import os
# 设置中文显示和样式
plt.rcParams["font.family"] = ["SimHei"]
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 设置随机种子保证结果可复现
random.seed(42)
np.random.seed(42)


# 1. 模拟生成财产保险业务数据
def generate_insurance_data():
    print("正在生成模拟数据...")
    # 生成日期范围：2024年1月1日到2024年12月31日
    dates = pd.date_range(start='2024-01-01', end='2024-12-31')

    # 生成机构数据
    agencies = ['北京分公司', '上海分公司', '广州分公司', '深圳分公司', '成都分公司']

    # 险种类型
    policy_types = ['车险', '财产险', '责任险', '意外险', '健康险']

    # 销售渠道
    channels = ['直销', '代理', '经纪', '电销', '网销']

    # 再保业务相关数据
    reinsurance_companies = ['再保公司A', '再保公司B', '再保公司C', '再保公司D', '再保公司E']
    reinsurance_types = ['比例合同', '非比例合同']
    reinsurance_methods = ['临时分保', '合同分保']
    regions = ['境内', '境外']
    relation_types = ['关联交易', '非关联交易']

    # 生成营销员数据
    salespersons = [f'SP{str(i).zfill(4)}' for i in range(1, 101)]

    # 创建DataFrame存储数据
    data = []

    for date in dates:
        for agency in agencies:
            # 营销员变动数据
            initial_count = 20  # 期初营销员数量
            new_added = random.randint(0, 5)  # 新增营销员
            resigned = random.randint(0, 3)  # 离职营销员

            # 保单数据
            for _ in range(random.randint(50, 100)):
                policy_type = random.choice(policy_types)
                channel = random.choice(channels)
                salesperson = random.choice(salespersons)

                # 保费和保额
                premium = round(random.uniform(1000, 50000), 2)
                amount = round(premium * random.uniform(50, 200), 2)

                # 再保相关数据
                reinsurance_company = random.choice(reinsurance_companies)
                reinsurance_type = random.choice(reinsurance_types)
                reinsurance_method = random.choice(reinsurance_methods)
                region = random.choice(regions)
                relation_type = random.choice(relation_types)
                ceded_premium = round(premium * random.uniform(0.1, 0.3), 2)  # 分出保费

                data.append({
                    'date': date,
                    'agency': agency,
                    'initial_salesperson_count': initial_count,
                    'new_salesperson_count': new_added,
                    'resigned_salesperson_count': resigned,
                    'policy_type': policy_type,
                    'channel': channel,
                    'salesperson': salesperson,
                    'premium': premium,
                    'amount': amount,
                    'reinsurance_company': reinsurance_company,
                    'reinsurance_type': reinsurance_type,
                    'reinsurance_method': reinsurance_method,
                    'region': region,
                    'relation_type': relation_type,
                    'ceded_premium': ceded_premium
                })

    df = pd.DataFrame(data)
    print(f"模拟数据生成完成，共{len(df)}条记录")
    return df


# 2. 计算各项指标
def calculate_metrics(df, period='month'):
    print(f"正在计算{period}度指标...")
    # 按指定周期聚合数据
    if period == 'day':
        df_period = df.copy()
        df_period['period'] = df_period['date'].dt.date
    elif period == 'week':
        df_period = df.copy()
        df_period['period'] = df_period['date'].dt.to_period('W').apply(lambda r: r.start_time.date())
    elif period == 'month':
        df_period = df.copy()
        df_period['period'] = df_period['date'].dt.to_period('M').apply(lambda r: r.start_time.date())
    elif period == 'year':
        df_period = df.copy()
        df_period['period'] = df_period['date'].dt.to_period('Y').apply(lambda r: r.start_time.date())
    else:
        raise ValueError("Invalid period. Choose from 'day', 'week', 'month', 'year'")

    # 按机构和周期分组
    grouped = df_period.groupby(['agency', 'period'])

    # 计算各项指标
    metrics = []

    for (agency, period_date), group in grouped:
        # 1. 营销员脱落率
        initial_count = group['initial_salesperson_count'].iloc[0]
        new_added = group['new_salesperson_count'].sum()
        resigned = group['resigned_salesperson_count'].sum()
        churn_rate = resigned / (initial_count + (new_added - resigned)) * 100 if (initial_count + (
                    new_added - resigned)) != 0 else 0

        # 2. 新单保额与新单保费比
        total_amount = group['amount'].sum()
        total_premium = group['premium'].sum()
        amount_premium_ratio = total_amount / total_premium if total_premium != 0 else 0

        # 3. 分险种保费占比
        policy_type_premium = group.groupby('policy_type')['premium'].sum()
        policy_type_percentage = (policy_type_premium / policy_type_premium.sum() * 100).to_dict()

        # 4. 险种组合变化率 (需要基期数据，这里简化处理)
        # 假设基期是上个月，实际应用中需要更复杂的处理
        policy_type_change_rate = 0  # 简化处理

        # 5. 分渠道保费占比
        channel_premium = group.groupby('channel')['premium'].sum()
        channel_percentage = (channel_premium / channel_premium.sum() * 100).to_dict()

        # 6. 业务来源集中度 (前3个再保公司)
        reinsurance_company_premium = group.groupby('reinsurance_company')['ceded_premium'].sum()
        top3_companies = reinsurance_company_premium.nlargest(3).sum()
        total_ceded_premium = reinsurance_company_premium.sum()
        concentration_ratio = (top3_companies / total_ceded_premium * 100) if total_ceded_premium != 0 else 0

        # 7. 非关联交易保费占比
        non_related_premium = group[group['relation_type'] == '非关联交易']['ceded_premium'].sum()
        non_related_percentage = (non_related_premium / total_ceded_premium * 100) if total_ceded_premium != 0 else 0

        # 8. 境内/境外保费占比
        domestic_premium = group[group['region'] == '境内']['ceded_premium'].sum()
        domestic_percentage = (domestic_premium / total_ceded_premium * 100) if total_ceded_premium != 0 else 0
        foreign_percentage = 100 - domestic_percentage

        # 9. 临分/合同保费占比
        facultative_premium = group[group['reinsurance_method'] == '临时分保']['ceded_premium'].sum()
        facultative_percentage = (facultative_premium / total_ceded_premium * 100) if total_ceded_premium != 0 else 0
        treaty_percentage = 100 - facultative_percentage

        # 10. 比例/非比例保费占比
        proportional_premium = group[group['reinsurance_type'] == '比例合同']['ceded_premium'].sum()
        proportional_percentage = (proportional_premium / total_ceded_premium * 100) if total_ceded_premium != 0 else 0
        non_proportional_percentage = 100 - proportional_percentage

        # 将结果存入字典
        metric_dict = {
            'agency': agency,
            'period': period_date,
            'churn_rate': round(churn_rate, 2),
            'amount_premium_ratio': round(amount_premium_ratio, 2),
            'policy_type_percentage': policy_type_percentage,
            'policy_type_change_rate': round(policy_type_change_rate, 2),
            'channel_percentage': channel_percentage,
            'concentration_ratio': round(concentration_ratio, 2),
            'non_related_percentage': round(non_related_percentage, 2),
            'domestic_percentage': round(domestic_percentage, 2),
            'foreign_percentage': round(foreign_percentage, 2),
            'facultative_percentage': round(facultative_percentage, 2),
            'treaty_percentage': round(treaty_percentage, 2),
            'proportional_percentage': round(proportional_percentage, 2),
            'non_proportional_percentage': round(non_proportional_percentage, 2)
        }

        metrics.append(metric_dict)

    metrics_df = pd.DataFrame(metrics)
    print(f"指标计算完成，共{len(metrics_df)}条记录")
    return metrics_df


# 3. 数据可视化
def visualize_metrics(metrics_df, output_dir='output'):
    print("正在生成可视化图表...")
    # 创建输出目录
    os.makedirs(output_dir, exist_ok=True)

    # 按机构分组
    grouped = metrics_df.groupby('agency')

    for agency, group in grouped:
        # 设置图表大小
        plt.figure(figsize=(15, 20))

        # 1. 营销员脱落率趋势
        plt.subplot(5, 2, 1)
        sns.lineplot(data=group, x='period', y='churn_rate')
        plt.title(f'{agency} - 营销员脱落率趋势')
        plt.xlabel('日期')
        plt.ylabel('脱落率(%)')
        plt.xticks(rotation=45)

        # 2. 新单保额与新单保费比趋势
        plt.subplot(5, 2, 2)
        sns.lineplot(data=group, x='period', y='amount_premium_ratio')
        plt.title(f'{agency} - 新单保额保费比趋势')
        plt.xlabel('日期')
        plt.ylabel('保额/保费')
        plt.xticks(rotation=45)

        # 3. 分险种保费占比(取第一个月数据)
        first_month = group.iloc[0]
        plt.subplot(5, 2, 3)
        pd.Series(first_month['policy_type_percentage']).plot.pie(autopct='%1.1f%%')
        plt.title(f'{agency} - 分险种保费占比')
        plt.ylabel('')

        # 4. 分渠道保费占比(取第一个月数据)
        plt.subplot(5, 2, 4)
        pd.Series(first_month['channel_percentage']).plot.pie(autopct='%1.1f%%')
        plt.title(f'{agency} - 分渠道保费占比')
        plt.ylabel('')

        # 5. 业务来源集中度
        plt.subplot(5, 2, 5)
        sns.lineplot(data=group, x='period', y='concentration_ratio')
        plt.title(f'{agency} - 业务来源集中度(前3大再保公司)')
        plt.xlabel('日期')
        plt.ylabel('集中度(%)')
        plt.xticks(rotation=45)

        # 6. 非关联交易占比
        plt.subplot(5, 2, 6)
        sns.lineplot(data=group, x='period', y='non_related_percentage')
        plt.title(f'{agency} - 非关联交易占比')
        plt.xlabel('日期')
        plt.ylabel('占比(%)')
        plt.xticks(rotation=45)

        # 7. 境内外占比
        plt.subplot(5, 2, 7)
        group[['period', 'domestic_percentage', 'foreign_percentage']].plot(x='period', kind='area', stacked=True)
        plt.title(f'{agency} - 境内外业务占比')
        plt.xlabel('日期')
        plt.ylabel('占比(%)')
        plt.xticks(rotation=45)

        # 8. 临分/合同占比
        plt.subplot(5, 2, 8)
        group[['period', 'facultative_percentage', 'treaty_percentage']].plot(x='period', kind='area', stacked=True)
        plt.title(f'{agency} - 临分/合同业务占比')
        plt.xlabel('日期')
        plt.ylabel('占比(%)')
        plt.xticks(rotation=45)

        # 9. 比例/非比例占比
        plt.subplot(5, 2, 9)
        group[['period', 'proportional_percentage', 'non_proportional_percentage']].plot(x='period', kind='area',
                                                                                         stacked=True)
        plt.title(f'{agency} - 比例/非比例业务占比')
        plt.xlabel('日期')
        plt.ylabel('占比(%)')
        plt.xticks(rotation=45)

        # 调整布局
        plt.tight_layout()

        # 保存图表
        plt.savefig(f'{output_dir}/{agency}_业务指标可视化.png', dpi=300, bbox_inches='tight')
        plt.close()

    print(f"可视化图表已保存到{output_dir}目录")


# 4. 控制台打印数据
def print_data_summary(metrics_df):
    print("\n业务指标数据摘要:")
    print(tabulate(metrics_df.head(), headers='keys', tablefmt='psql', showindex=False))

    print("\n关键指标统计描述:")
    numeric_cols = metrics_df.select_dtypes(include=[np.number]).columns
    print(tabulate(metrics_df[numeric_cols].describe(), headers='keys', tablefmt='psql'))


# 5. 保存数据到文件
def save_data(insurance_data, metrics_df,data = 'data'):
    print("正在保存数据到文件...")
    #创建数据目录
    os.makedirs(data, exist_ok=True)

    # 保存原始数据
    insurance_data.to_csv(f'{data}/原始业务数据.csv', index=False, encoding='utf_8_sig')

    # 保存指标数据
    metrics_df.to_csv(f'{data}/业务指标数据.csv', index=False, encoding='utf_8_sig')
    metrics_df.to_excel(f'{data}/业务指标数据.xlsx', index=False)

    print(f"数据已保存到{data}目录")


# 主函数
def main():
    # 生成数据
    insurance_data = generate_insurance_data()

    # 计算指标
    monthly_metrics = calculate_metrics(insurance_data, period='month')

    # 控制台打印
    print_data_summary(monthly_metrics)

    # 可视化
    visualize_metrics(monthly_metrics)

    # 保存数据
    save_data(insurance_data, monthly_metrics)

    print("\n所有处理完成！结果已保存到output目录")


if __name__ == "__main__":
    main()